﻿
CREATE PROCEDURE dbo._CopyObject
(@DBName sysname,
 @ObjectName sysname
)AS SET NOCOUNT ON

SET NOCOUNT ON

CREATE TABLE #Text (Text varchar(8000))
DECLARE @Exec varchar(8000)
SET @Exec = --'exec '++'.dbo.sp_helptext '+@ObjectName
'SELECT ctext FROM '+@DBName+'.dbo.syscomments c inner join '+@DBName+'.dbo.sysobjects o on c.id = o.id WHERE name = '''++@ObjectName+''''


INSERT INTO #Text	EXEC(@Exec)

--DECLARE @Text varchar(8000),@SQL varchar(8000)DECLARE cur CURSOR LOCAL FOR SELECT Text FROM #TEXT OPEN cur FETCH NEXT FROM cur INTO @Text WHILE @@fetch_status = 0 BEGIN 	SET @SQL = ISNULL(@SQL,'')+@Text	FETCH NEXT FROM cur INTO @Text END

SELECT @EXEC = dbo._CreateOrAlter(@ObjectName,Text) FROM #Text

PRINT '
***********************************************************
'+SUBSTRING(@EXEC,1,128)+'
***********************************************************'

EXEC (@EXEC)


